﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

using MySql.Data.MySqlClient;

using MakeIT.MiraPark.Comuns;

namespace MakeIT.MiraPark.Negocios
{
    public static partial class ModelBO
    {

        public static HorarioVO getHorario(int idHorario)
        {
            List<HorarioVO> listHorarioVOs = getHorarios(idHorario, null);
            if (listHorarioVOs != null && listHorarioVOs.Count > 0)
                return listHorarioVOs[0];
            else
                return null;
        }
        public static List<HorarioVO> getHorarios()
        {
            return getHorarios(null, null);
        }
        public static List<HorarioVO> getHorarios(string tipoPeriodo)
        {
            return getHorarios(null, tipoPeriodo);
        }

        public static List<HorarioVO> getHorarios(int? idHorario, string tipoPeriodo)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT IdHorario, Controle, TipoPeriodo, HoraInicial, HoraFinal, HoraInicialDiaria, HoraFinalDiaria, HoraInicialSab, HoraFinalSab, HoraInicialDom, HoraFinalDom ");
            query.AppendLine("FROM horario ");
            if (idHorario != null)
            {
                query.AppendLine(where + "IdHorario=?IdHorario ");
                where = "AND ";
            }
            if (tipoPeriodo != null)
            {
                query.AppendLine(where + "TipoPeriodo=?TipoPeriodo ");
                where = "AND ";
            }
            query.AppendLine("ORDER BY Controle ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (idHorario != null) cmd.Parameters.AddWithValue("?IdHorario", idHorario);
            if (tipoPeriodo != null) cmd.Parameters.AddWithValue("?TipoPeriodo", tipoPeriodo);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                List<HorarioVO> listHorario = new List<HorarioVO>();

                while (reader.Read())
                {
                    HorarioVO horarioVO = HorarioVO.CreateHorarioVO();
                    horarioVO.idHorario = reader.GetInt32("IdHorario");
                    horarioVO.controle = reader.GetString("Controle");
                    horarioVO.tipoPeriodo = reader.GetString("TipoPeriodo");
                    horarioVO.horaInicial = reader.GetTimeSpan("HoraInicial");
                    horarioVO.horaFinal = reader.GetTimeSpan("HoraFinal");
                    horarioVO.horaInicialDiaria = reader.GetTimeSpan("HoraInicialDiaria");
                    horarioVO.horaFinalDiaria = reader.GetTimeSpan("HoraFinalDiaria");
                    horarioVO.horaInicialSab = reader.GetTimeSpan("HoraInicialSab");
                    horarioVO.horaFinalSab = reader.GetTimeSpan("HoraFinalSab");
                    horarioVO.horaInicialDom = reader.GetTimeSpan("HoraInicialDom");
                    horarioVO.horaFinalDom = reader.GetTimeSpan("HoraFinalDom");

                    listHorario.Add(horarioVO);
                }
                reader.Close();
                return listHorario;
            }
            catch (Exception ex){ throw ex; }
            finally { cmd.Connection.Close(); }
        }

        public static void setHorario(HorarioVO horarioVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("UPDATE horario ");
            query.AppendLine("SET Controle=?Controle, TipoPeriodo=?TipoPeriodo, HoraInicial=?HoraInicial, HoraFinal=?HoraFinal, HoraInicialDiaria=?HoraInicialDiaria, HoraFinalDiaria=?HoraFinalDiaria, HoraInicialSab=?HoraInicialSab, HoraFinalSab=?HoraFinalSab, HoraInicialDom=?HoraInicialDom, HoraFinalDom=?HoraFinalDom ");
            query.AppendLine("WHERE IdHorario=?IdHorario ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdHorario", horarioVO.idHorario);
            cmd.Parameters.AddWithValue("?Controle", horarioVO.controle);
            cmd.Parameters.AddWithValue("?TipoPeriodo", horarioVO.tipoPeriodo);
            cmd.Parameters.AddWithValue("?HoraInicial", horarioVO.horaInicial);
            cmd.Parameters.AddWithValue("?HoraFinal", horarioVO.horaFinal);
            cmd.Parameters.AddWithValue("?HoraInicialDiaria", horarioVO.horaInicialDiaria);
            cmd.Parameters.AddWithValue("?HoraFinalDiaria", horarioVO.horaFinalDiaria);
            cmd.Parameters.AddWithValue("?HoraInicialSab", horarioVO.horaInicialSab);
            cmd.Parameters.AddWithValue("?HoraFinalSab", horarioVO.horaFinalSab);
            cmd.Parameters.AddWithValue("?HoraInicialDom", horarioVO.horaInicialDom);
            cmd.Parameters.AddWithValue("?HoraFinalDom", horarioVO.horaFinalDom);
            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static long newHorario(HorarioVO horarioVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO horario ");
            query.AppendLine("SET IDHorario=?IdHorario, Controle=?Controle, TipoPeriodo=?TipoPeriodo, HoraInicial=?HoraInicial, HoraFinal=?HoraFinal, HoraInicialDiaria=?HoraInicialDiaria, HoraFinalDiaria=?HoraFinalDiaria, HoraInicialSab=?HoraInicialSab, HoraFinalSab=?HoraFinalSab, HoraInicialDom=?HoraInicialDom, HoraFinalDom=?HoraFinalDom ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdHorario", horarioVO.idHorario);
            cmd.Parameters.AddWithValue("?Controle", horarioVO.controle);
            cmd.Parameters.AddWithValue("?TipoPeriodo", horarioVO.tipoPeriodo);
            cmd.Parameters.AddWithValue("?HoraInicial", horarioVO.horaInicial);
            cmd.Parameters.AddWithValue("?HoraFinal", horarioVO.horaFinal);
            cmd.Parameters.AddWithValue("?HoraInicialDiaria", horarioVO.horaInicialDiaria);
            cmd.Parameters.AddWithValue("?HoraFinalDiaria", horarioVO.horaFinalDiaria);
            cmd.Parameters.AddWithValue("?HoraInicialSab", horarioVO.horaInicialSab);
            cmd.Parameters.AddWithValue("?HoraFinalSab", horarioVO.horaFinalSab);
            cmd.Parameters.AddWithValue("?HoraInicialDom", horarioVO.horaInicialDom);
            cmd.Parameters.AddWithValue("?HoraFinalDom", horarioVO.horaFinalDom);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
                return cmd.LastInsertedId;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static void delHorario(HorarioVO horarioVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM horario ");
            query.AppendLine("WHERE IdHorario=?IdHorario ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdHorario", horarioVO.idHorario);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static bool readyToDeleteHorario(int idHorario)
        {
            int contagem = 0;

            MySqlDataReader reader = null;
            StringBuilder query = new StringBuilder();
            MySqlCommand cmd = getConnection();
            cmd.Connection.Open();

            try
            {

                query.AppendLine("SELECT count(*) contagem FROM tabelaavulso WHERE IdHorario=?IdHorario ");
                cmd.CommandText = query.ToString();
                cmd.Parameters.AddWithValue("?IdHorario", idHorario);
                reader = cmd.ExecuteReader();
                reader.Read();
                contagem = reader.GetInt32("contagem");
                reader.Close();

                query = new StringBuilder();
                query.AppendLine("SELECT count(*) contagem FROM tabelaconvenio WHERE IdHorario=?IdHorario ");
                cmd.CommandText = query.ToString();
                reader = cmd.ExecuteReader();
                reader.Read();
                contagem += reader.GetInt32("contagem");
                reader.Close();

                query = new StringBuilder();
                query.AppendLine("SELECT count(*) contagem FROM tabelamensal WHERE IdHorario=?IdHorario ");
                cmd.CommandText = query.ToString();
                reader = cmd.ExecuteReader();
                reader.Read();
                contagem += reader.GetInt32("contagem");
                reader.Close();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

            return contagem == 0;

        }
    
    }
}
